"""SQLite存储实现"""

""" 
需要添加的表有
状态表：
    id,type, status,create_time   
    goods_1,goodsm,unload_truck/type_pool/normal_pool/pickup_pool/agv/exit_pool/load_truck
    truck_1,unload_truck,spawning,waiting,loading/unloading,finish
    agv_1,agv,spawning/waiting/loading/unloading/finish

货物表：
    id, type, infos, create_time
载具表：
    id,type, goods_list, infos, create_time  status_list:sapwning,waiting,loading/unloading,finish  type:truck,agv


"""
from optparse import Option
import sqlite3
from dataclasses import dataclass,field
from typing import List,Dict,Any, Optional

@dataclass
class SQLiteStorage:
    db_path: str=field(default="./warehouse.db")
    connection:Optional[ sqlite3.Connection] = None
    cursor:Optional[sqlite3.Cursor]=None

    def __post_init__(self):
        self._connect()
        self._create_tables()

    def _connect(self):
        try:
            self.connection = sqlite3.connect(self.db_path)
            # 启动外键约束 当前的表的id来自两个不同的表，所以暂时不做这个限制
            # self.connection.execute("PRAGMA foreign_keys = ON")
            # 行工厂 使查询出的结果以字典形式返回
            self.connection.row_factory = sqlite3.Row
            self.cursor = self.connection.cursor()
        except sqlite3.Error as e:
            print(f"数据库连接失败:{e}")
    
    def _create_tables(self):
        """ 创建数据库表 """
        if self.connection is None or self.cursor is None : return

        try:
            CREATE_TABLE_SQL_Goods="""
            create table if not exists goods (
            id TEXT PRIMARY KEY,
            type TEXT NOT NULL,
            infos TEXT ,
            create_time TEXT
            )
            """
            CREATE_TABLE_SQL_Vehicle="""
            create table if not exists vehicle (
            id TEXT PRIMARY KEY,
            type TEXT NOT NULL,
            infos TEXT ,
            create_time TEXT
            )
            """
            # id 变成自增的 integer
            # obj_id 的外键约束来自与 goods id 和 vehicle id 两个表的id
            CREATE_TABLE_SQL_Status="""
            create table if not exists status (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            obj_id TEXT ,
            type TEXT NOT NULL,
            status TEXT ,
            create_time TEXT
            )
            """

            self.cursor.execute(CREATE_TABLE_SQL_Goods)
            self.cursor.execute(CREATE_TABLE_SQL_Vehicle)
            self.cursor.execute(CREATE_TABLE_SQL_Status)
        except sqlite3.Error as e:
            print(f"创建数据库表失败:{e}")
    
    def close(self):
        if self.connection: self.connection.close()

    def _convert_row_to_dict(self, row)->Dict[str,Any]:
        """ 将数据库行转换为字典 """
        if row is None: return {}
        return {key:row[key] for key in row.keys()}
    # 通用数据库操作
    def execute_query(self,query:str,params:tuple=())-> List[Dict[str,Any]]:
        """ 执行查询语句 , 返回结果列表"""
        try:
            if self.connection is None or self.cursor is None : return []
            self.cursor.execute(query,params)
            rows=self.cursor.fetchall()
            return [self._convert_row_to_dict(row)for row in rows]
        except sqlite3.Error as e:
            print(f"执行查询语句失败:{e}")
            return []
    
    def excute_insert(self,query:str,params:tuple=())-> Optional[int]:
        """ 执行插入语句 返回最后插入的行id """
        if self.connection is None or self.cursor is None : return None
        try:
            self.cursor.execute(query,params)
            self.connection.commit()
            return self.cursor.lastrowid  
        except sqlite3.Error as e:

            print(f"[ERROR-DB] 执行插入语句失败:{e}")
            print(f"[ERROR-DB] 详细错误信息：{type(e).__name__},{str(e)}")
            # 打印具体的参数问题
            for i ,param in enumerate(params):
                print(f"[ERROR-DB] 参数 {i} :{param} (类型：{type(param).__name__})")

            self.connection.rollback()
            return None

    def excute_insert_batch(self,query:str,params_list:List[tuple])->bool:
        """执行批量插入，返回是否成功

        Args:
            query (str): _description_
            params_list (List[tuple]): _description_

        Returns:
            bool: _description_
        """
        if self.connection is None or self.cursor is None : return False

        try:
            self.cursor.executemany(query,params_list)
            self.connection.commit()
            return True
        except Exception as e:
            print(f"批量插入数据失败:{e}")
            return False


    def excute_update(self,query:str,params:tuple=())->bool:
        """ 执行更新语句 返回是否成功 """
        if self.connection is None or self.cursor is None : return False
        try:
            self.cursor.execute(query,params)
            self.connection.commit()
            return True
        except sqlite3.Error as e:
            print(f"[ERROR-DB] 执行更新语句失败:{e}")
            self.connection.rollback()
            return False
    
    def excute_delete(self,query:str,params:tuple=())->bool:

        """ 执行删除语句 删除成功返回True """
        if self.connection is None or self.cursor is None : return False
        try:
            self.cursor.execute(query,params)
            self.connection.commit()
            return True
        except sqlite3.Error as e:
            print(f"[ERROR-DB] 执行删除语句失败:{e}")
            self.connection.rollback()
            return False
    
    def clear(self)->bool:
        """ 清空数据库 """
        if self.connection is None or self.cursor is None : return False
        try:
            self.cursor.execute("drop table if exists goods")
            self.cursor.execute("drop table if exists vehicle")
            self.cursor.execute("drop table if exists status")
            
            # self.cursor.execute("select name from sqlite_master where type='table'")
            # tables=[row['name'] for row in self.cursor.fetchall()]
            # for table in tables:
            #     self.cursor.execute(f"delete from  {table}")

            self.connection.commit()
            return True
        except sqlite3.Error as e:
            print(f"[ERROR-DB] 清空数据库失败:{e}")
            self.connection.rollback()
            return False
        


    



    

    
    

    
